package com.loongtech.bi.action.advertisment;

import com.loongtech.bi.advertisment.controller.BaseAdvAction;
import com.loongtech.bi.advertisment.service.AdvUserDataService;
import com.loongtech.bi.manager.flow.HadoopQueryManager;
import com.loongtech.bi.manager.system.SysUserManager;
import com.loongtech.core.util.Utils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.Serializable;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

/**
 * 
 * ClassName: AdvRoleDataAction
 * 
 * @Description: TODO 广告账号VIP统计
 * @author liuchen
 * @date 2017-6-22 上午11:40:41
 */
@Controller
@Scope("prototype")
@RequestMapping("/market/advUserVipDataMarket")
public class AdvUserVipDataBiAction extends BaseAdvAction {

	/**
	 * 
	 */
	private static final long serialVersionUID = -6662336350910337632L;

	private List<ColumnModel> columns;

	private int maxviplevel = 8;

	private List<Object[]> results; // 结果List

	private List<Object[]> resultRate; // 结果List

	@Resource(name = "advUserDataService")
	private AdvUserDataService service;

    @Autowired
    private SysUserManager sysUserManager;

	@Autowired
	HadoopQueryManager hadoopQueryManager;

	@RequestMapping(value = "initParam.do", method = { RequestMethod.GET, RequestMethod.POST })
	public void initParam(HttpServletRequest request, HttpServletResponse response, @RequestParam String uid, String callback) {
		try {
            Map<String, Object> result = new HashMap<String, Object>();
            Integer userid = sysUserManager.getUserIdByName(uid);
            if (null == userid) {
                result.put("res", 1);
                result.put("msg", "帐户错误，请联系管理员!");
                printToJson(response, result, callback);
                return;
            }
            initData(userid);

			result.put("res", "0");
			result.put("platform", getPlatform());
			result.put("agent", getAgent());
			result.put("paytype", getPaytype());
			result.put("channelName", getChannelName());
			printToJson(response, result, callback);
		} catch (Exception e) {
			e.printStackTrace();
			Map<String, Object> result = new HashMap<String, Object>();
			result.put("res", "1");
			result.put("msg", e.getMessage());
			printToJson(response, result, callback);
			return;
		}
	}

	@RequestMapping(value = "search.do", method = { RequestMethod.GET, RequestMethod.POST })
	public void search(HttpServletRequest request, HttpServletResponse response, @RequestParam String day,
			@RequestParam String sday, @RequestParam String eday, @RequestParam String agent,
			@RequestParam String platform, @RequestParam String channelName, @RequestParam String paytype,@RequestParam String uid,
                       String callback) {
		try {
			columns = new ArrayList<ColumnModel>();

            Map<String, Object> re = new HashMap<String, Object>();
            Integer userid = sysUserManager.getUserIdByName(uid);
            if (null == userid) {
                re.put("res", 1);
                re.put("msg", "帐户错误，请联系管理员!");
                printToJson(response, re, callback);
                return;
            }
            initData(userid);

			if (hasAgents == null || hasAgents.size() <= 0) {
				re.put("res", "1");
				re.put("msg", "此用户无任何代理商权限，请联系管理员配置!");
				printToJson(response, re, callback);
				return;
			}

			try {
				maxviplevel = service.getVipLevel(1);
			} catch (Exception e) {
				maxviplevel = 10;
				e.printStackTrace();
			}
			columns.add(new ColumnModel("渠道", "0"));
			columns.add(new ColumnModel("新增账号", "1"));
			for (int i = 0; i <= maxviplevel; i++) {
				columns.add(new ColumnModel("VIP" + i, i + 2 + ""));
			}

			if (HadoopQueryManager.VISIT_QUEUE.offer("AdvUserVipDataBiAction")) {
				System.out.println("VISIT_QUEUE offer AdvUserVipDataBiAction ok!");

                String[] channelNames = "".equals(channelName) ? getChannelName().toArray(new String[getChannelName().size()]) : Utils.matchesString(channelName).split(",");
                String[] agents = "".equals(agent) ? getAgent().toArray(new String[getAgent().size()]) : Utils.matchesString(agent).split(",");
                String[] platforms = "".equals(platform) ? getPlatform().toArray(new String[getPlatform().size()]) : Utils.matchesString(platform).split(",");
                String[] paytypes = "".equals(paytype) ? getPaytype().toArray(new String[getPaytype().size()]) : Utils.matchesString(paytype).split(",");

                StringBuffer sql = new StringBuffer();
				sql.append("select a.cname channelName,count(a.userid) newAccount  ");
				sql.append(getColumnSelect());
				sql.append("from  ");
				sql.append("(select min(nvl(bi.channelname,t.url))cname,userid from bi_adv_totalusercreate t ");
				sql.append("left join bi_advconfig bi on t.url = bi.id ");
				sql.append("where dt = (select max(dt) from bi_adv_totalusercreate) and t.createday >= '" + sday
						+ "' and t.createday <= '" + eday + "' ");
				if (channelNames != null && channelNames.length != 0) {
					sql.append("and channelName in (" + getParams(channelNames) + ")");
				}
				if (agents != null && agents.length != 0) {
					sql.append("and agent in (" + getParams(agents) + ")");
				}
				if (platforms != null && platforms.length != 0) {
					sql.append("and platform in (" + getParams(platforms) + ")");
				}
				if (paytypes != null && paytypes.length != 0) {
					sql.append("and paytype in (" + getParams(paytypes) + ")");
				}
				sql.append("group by userid)a ");
				sql.append("left join (select userid,max(viplevel)viplevel from roleinfo where dt = '" + day
						+ "' group by userid)b on a.userid = b.userid ");
				sql.append("group by a.cname ");
				sql.append("order by newAccount ");

				Entry<List<String>, List<Object[]>> ret = hadoopQueryManager.executeSQL(sql.toString());
				if (null == ret || null == ret.getKey() || ret.getKey().isEmpty() || null == ret.getValue()) {
					re.put("res", "1");
					re.put("msg", "查询结果为空!");
					printToJson(response, re, callback);
					return;
				}
				results = changeNum(ret.getValue());
			} else {
				System.out.println("VISIT_QUEUE offer AdvUserVipDataBiAction failed!");
				re.put("res", "1");
				re.put("msg", "当前系统正被访问，为保证服务器稳定，请稍后再试");
				printToJson(response, re, callback);
				return;
			}
			resultRate = new ArrayList<Object[]>();
			DecimalFormat df = new DecimalFormat("0.000");
			for (Object[] o : results) {
				Object[] orate = new Object[o.length];
				for (int i = 0; i < o.length; i++) {
					if (i < 2) {
						orate[i] = o[i];
					} else {
						orate[i] = ("0".equals(String.valueOf(o[1])) || "".equals(String.valueOf(o[1]))) ? "0" : String
								.valueOf(df.format(Float.valueOf(String.valueOf(o[i]))
										/ Float.valueOf(String.valueOf(o[1]))));
					}
				}
				resultRate.add(orate);
			}

			re.put("res", "0");
			re.put("columns", columns);
			re.put("accounts", results);
			re.put("accountRate", resultRate);
			printToJson(response, re, callback);
		} catch (Exception e) {
			e.printStackTrace();
			Map<String, Object> re = new HashMap<String, Object>();
			re.put("res", "1");
			re.put("msg", e.getMessage());
			printToJson(response, re, callback);
			return;
		}
	}

	private List<Object[]> changeNum(List<Object[]> re) {
		List<Object[]> r = new ArrayList<Object[]>();
		for (Object[] o : re) {
			Object[] oi = new Object[o.length];
			for (int i = 0; i < o.length; i++) {
				if (i < 1) {
					oi[i] = o[i];
				} else {
					oi[i] = Integer.valueOf(((String) o[i]));
				}
			}
			r.add(oi);
		}
		return r;
	}

	private String getColumnSelect() {
		StringBuilder s = new StringBuilder();
		for (int i = 0; i <= maxviplevel; i++) {
			s.append(" ,count(case when b.viplevel = " + i + " then a.userid  end) as v" + i + " ");
		}
		return s.toString();
	}

	private String getParams(String[] param) throws Exception {
		StringBuilder s = new StringBuilder();
		for (String str : param) {
			s.append("'" + str + "',");
		}
		return s.substring(0, s.length() - 1);
	}

	public class ColumnModel implements Serializable {

		private static final long serialVersionUID = 1L;
		private String header;
		private String property;

		public ColumnModel(String header, String property) {
			this.header = header;
			this.property = property;
		}

		public String getHeader() {
			return header;
		}

		public String getProperty() {
			return property;
		}
	}
}
